﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using jiaju.Areas.Back.Models.DBModel;
using jiaju.Areas.Back.Models;
using System.Data.SqlClient;
namespace jiaju.Areas.Back.Models.DAL
{
    public class NewsService
    {
        public static List<NewsType> NewsTypet()
        {
            string sql = "select * from dbo.NewsType";
            List<NewsType> list = new List<NewsType>();
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            while (sdr.Read())
            {
                int Id = (int)sdr["Id"];
                string Name = (string)sdr["Name"];
                NewsType banner = new NewsType()
                {
                    Id = Id,
                    Name = Name
                };
                list.Add(banner);
            }
            return list;
        }
        public static List<News> getArtList(int page, int limit, string title, string Flag,string Type)
        {
            String sql = string.Format("select top({1}) Id,Title,Time,Cont,TypeId,HomeFlag,(select Name  from dbo.newsType where Id=TypeId) as TypeName from [dbo].[News] where Id not in (select top(({0}-1)*{1}) Id from [dbo].[News])", page, limit);
            if (!string.IsNullOrEmpty(title))
            {
                sql = sql + string.Format(" and Title like '%{0}%'", title);
            }
            if (!string.IsNullOrEmpty(Flag))
            {             
                sql = sql + string.Format(" and HomeFlag ='{0}'", Flag);
            }
            if (!string.IsNullOrEmpty(Type))
            {            
                sql = sql + " and TypeId ="+ Type;
            }
            sql = sql + "order by Id desc";
            List<News> list = new List<News>();
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            while (sdr.Read())
            {
                int Id = (int)sdr["Id"];
                string Title = (string)sdr["Title"];
                DateTime Time = (DateTime)sdr["Time"];
                string Cont = (string)sdr["Cont"];
                int TypeId = (int)sdr["TypeId"];
                string HomeFlag = (string)sdr["HomeFlag"];
                string TypeName = (string)sdr["TypeName"];
                News art = new News()
                {
                    Id = Id,
                    Title = Title,
                    Time = Time,                 
                    Cont = Cont,
                    TypeId = TypeId,
                    HomeFlag = HomeFlag,
                    TypeName= TypeName,
                };
                list.Add(art);
            }
            return list;
        }
        public static int AddNews(News art)
        {
            string sql = string.Format(@"INSERT INTO [dbo].[News]
           (Title  
           ,Time
           ,Cont
           ,TypeId
           ,HomeFlag)
            VALUES
           ('{0}','{1}','{2}',{3},'{4}')", art.Title,DateTime.Now, art.Cont, art.TypeId, art.HomeFlag);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        public static int DelNews(string Id)
        {

            string sql = "delete from dbo.News where Id=" + Id;
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        public static News GetById(string Id)
        {
            string sql = "select * from dbo.News where Id=" + Id;
            News news = null;
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            while (sdr.Read())
            {
                int id = (int)sdr["Id"];
                string Title = (string)sdr["Title"];
                DateTime Time = (DateTime)sdr["Time"];
                string Cont = (string)sdr["Cont"];
                int TypeId = (int)sdr["TypeId"];
                string HomeFlag = (string)sdr["HomeFlag"];
                news = new News()
                {
                    Id = id,
                    Title = Title,
                    Time = Time,               
                    Cont = Cont,
                    TypeId = TypeId,
                    HomeFlag = HomeFlag,
                };

            }
            return news;

        }
        public static int EditNews(News art)
        {
            string sql = String.Format(@"UPDATE [dbo].[News]
                                  SET Title ='{0}'                                 
                                  ,Cont = '{1}'
                                  ,TypeId = {2}
                                  ,HomeFlag = '{3}'                                 
                                  WHERE Id={4}",
                                 art.Title,
                                 art.Cont,
                                 art.TypeId,
                                 art.HomeFlag,
                                 art.Id);
            int num = DBHelp.ExecuteNonQuery(sql);
            return num;
        }
        public static List<NewsType> getNewsTypeList(int page, int limit, string name)
        {
            string sql = string.Format("select top({0}) * from dbo.newsType where Id not in (select top(({1}-1) * {0}) Id from dbo.newsType)", limit, page);
            if (!string.IsNullOrEmpty(name))
            {
                sql = sql + string.Format("and Name like '%{0}%'", name);
            }
            List<NewsType> list = new List<NewsType>();
            SqlDataReader sdr = DBHelp.ExecuteReader(sql);
            while (sdr.Read())
            {
                int Id = (int)sdr["Id"];
                string Name = (string)sdr["Name"];
                NewsType newtype = new NewsType()
                {
                    Id = Id,
                    Name = Name,
                };
                list.Add(newtype);
            }
            return list;
        }
    }
}